#[cfg(test)]
mod tests {
    use super::super::dao::UserDAO;
    use super::super::model::User;
    use sqlx::{sqlite::SqlitePool};
    use std::sync::Arc;

    async fn setup_db() -> Arc<SqlitePool> {
        // 使用内存数据库进行测试
        let pool = SqlitePool::connect("sqlite::memory:").await.unwrap();
        
        // 创建用户表
        let create_table_sql = "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            sex TEXT NOT NULL
        )";
        sqlx::query(create_table_sql)
            .execute(&pool)
            .await
            .unwrap();

        Arc::new(pool)
    }

    #[tokio::test]
    async fn test_create_and_get_user() {
        let db = setup_db().await;
        let user_dao = UserDAO::new(db);
        
        // 创建用户
        let user = User {
            id: None,
            name: "Test User".to_string(),
            sex: "Male".to_string(),
        };
        
        let user_id = user_dao.create_user(&user).await.unwrap();
        assert!(user_id > 0);
        
        // 获取用户
        let fetched_user = user_dao.get_user(user_id).await.unwrap().unwrap();
        assert_eq!(fetched_user.name, "Test User");
        assert_eq!(fetched_user.sex, "Male");
    }

    #[tokio::test]
    async fn test_list_users() {
        let db = setup_db().await;
        let user_dao = UserDAO::new(db);
        
        // 创建几个测试用户
        let user1 = User {
            id: None,
            name: "User 1".to_string(),
            sex: "Male".to_string(),
        };
        
        let user2 = User {
            id: None,
            name: "User 2".to_string(),
            sex: "Female".to_string(),
        };
        
        user_dao.create_user(&user1).await.unwrap();
        user_dao.create_user(&user2).await.unwrap();
        
        // 获取所有用户
        let users = user_dao.list_users().await.unwrap();
        assert_eq!(users.len(), 2);
    }

    #[tokio::test]
    async fn test_update_user() {
        let db = setup_db().await;
        let user_dao = UserDAO::new(db);
        
        // 创建用户
        let user = User {
            id: None,
            name: "Original Name".to_string(),
            sex: "Male".to_string(),
        };
        
        let user_id = user_dao.create_user(&user).await.unwrap();
        
        // 更新用户
        let updated_user = User {
            id: None,
            name: "Updated Name".to_string(),
            sex: "Female".to_string(),
        };
        
        let result = user_dao.update_user(user_id, &updated_user).await.unwrap();
        assert!(result);
        
        // 验证更新
        let fetched_user = user_dao.get_user(user_id).await.unwrap().unwrap();
        assert_eq!(fetched_user.name, "Updated Name");
        assert_eq!(fetched_user.sex, "Female");
    }

    #[tokio::test]
    async fn test_delete_user() {
        let db = setup_db().await;
        let user_dao = UserDAO::new(db);
        
        // 创建用户
        let user = User {
            id: None,
            name: "Test User".to_string(),
            sex: "Male".to_string(),
        };
        
        let user_id = user_dao.create_user(&user).await.unwrap();
        
        // 删除用户
        let result = user_dao.delete_user(user_id).await.unwrap();
        assert!(result);
        
        // 验证用户已删除
        let fetched_user = user_dao.get_user(user_id).await.unwrap();
        assert!(fetched_user.is_none());
    }

    #[tokio::test]
    async fn test_get_nonexistent_user() {
        let db = setup_db().await;
        let user_dao = UserDAO::new(db);
        
        // 尝试获取不存在的用户
        let fetched_user = user_dao.get_user(999).await.unwrap();
        assert!(fetched_user.is_none());
    }

    #[tokio::test]
    async fn test_update_nonexistent_user() {
        let db = setup_db().await;
        let user_dao = UserDAO::new(db);
        
        // 尝试更新不存在的用户
        let user = User {
            id: None,
            name: "Test User".to_string(),
            sex: "Male".to_string(),
        };
        
        let result = user_dao.update_user(999, &user).await.unwrap();
        assert!(!result);
    }

    #[tokio::test]
    async fn test_delete_nonexistent_user() {
        let db = setup_db().await;
        let user_dao = UserDAO::new(db);
        
        // 尝试删除不存在的用户
        let result = user_dao.delete_user(999).await.unwrap();
        assert!(!result);
    }
}